﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Linq;
using System.Text;
using MyClass;
using MYEXCEL = Microsoft.Office.Interop;
using System.Reflection;    //引用这个才能使用Missing字段
using System.IO;
using System.Windows.Forms;

namespace GPS_DB
{
    class XlsCheck
    {
        public ArrayList sourceA = new ArrayList();
        public ArrayList sourceB = new ArrayList();
        public ArrayList sourceC = new ArrayList();
        public ArrayList targetA = new ArrayList();
        public ArrayList targetB = new ArrayList();
        public ArrayList targetC = new ArrayList();
        public string SavingFilename;

        public XlsCheck()
        {
            SavingFilename = Application.StartupPath + @"\RecDataReport\" + "xlsCheckReport.txt";

            OpenFileDialog openf1 = new OpenFileDialog();
            openf1.Filter = "GPS XLS(.xls)|*.xls;*.xlsx";
            openf1.Title = "找开xlsCheck.xlsx文件";
            openf1.InitialDirectory = LrnS.GetItem("xlscheck");
            openf1.Multiselect = false;
            if (openf1.ShowDialog() == DialogResult.OK)
            {
                LrnS.SaveItem("xlscheck", Path.GetDirectoryName(openf1.FileName));
                try
                {
                    ReadIn(openf1.FileName);
                }
                catch (Exception er)
                {
                    MessageBox.Show("dispatch Error: " + er.Message.ToString());
                }
            }
        }

        private void ReadIn(string path)
        {
            sourceA.Clear();
            sourceB.Clear();
            sourceC.Clear();
            targetA.Clear();
            targetB.Clear();
            targetC.Clear();

            string sa = "", sb = "", sc = "", ta = "", tb = "", tc = "";

            DataSet ds = GF.DsFromExcel(path);
            if (!ds.Tables.Contains("source$"))
                return;
            for (int i = 0; i < ds.Tables["source$"].Rows.Count; i++)
            {
                sa = ds.Tables["source$"].Rows[i][0].ToString().Trim();
                sb = ds.Tables["source$"].Rows[i][1].ToString().Trim();
                sc = ds.Tables["source$"].Rows[i][2].ToString().Trim();
                if (sa == "")
                    break;
                sourceA.Add(sa);
                sourceB.Add(sb);
                sourceC.Add(sc);

            }
            if (!ds.Tables.Contains("target$"))
                return;
            for (int i = 0; i < ds.Tables["target$"].Rows.Count; i++)
            {
                ta = ds.Tables["target$"].Rows[i][0].ToString().Trim();
                tb = ds.Tables["target$"].Rows[i][1].ToString().Trim();
                tc = ds.Tables["target$"].Rows[i][2].ToString().Trim();
                if (ta == "")
                    break;
                targetA.Add(ta);
                targetB.Add(tb);
                targetC.Add(tc);
            }
        }

        public void t_Have_s()
        {
            string result = "";
            for (int i = 0; i < sourceA.Count; i++)
            {
                for (int j = 0; j < targetA.Count; j++)
                {
                    if ((string)sourceA[i] == (string)targetA[j] && 
                        (string)sourceB[i] == (string)targetB[j] && 
                        (string)sourceC[i] == (string)targetC[j])
                        break;
                    if (j == targetA.Count - 1)
                        result += ((string)sourceA[i]).PadRight(6, '　') + (string)sourceB[i] + (string)sourceC[i] + " \r\n";
                }
            }
            result += "**********************************************************************************\r\n";
            for (int i = 0; i < targetA.Count; i++)
            {
                for (int j = 0; j < sourceA.Count; j++)
                {
                    if ((string)targetA[i] == (string)sourceA[j] &&
                        (string)targetB[i] == (string)sourceB[j] &&
                        (string)targetC[i] == (string)sourceC[j])
                        break;
                    if (j == sourceA.Count - 1)
                        result += ((string)targetA[i]).PadRight(6, '　') + (string)targetB[i] + (string)targetC[i] + " \r\n";
                }
            }
            saving(result);
            System.Diagnostics.Process.Start(SavingFilename);
        }

        public void missingPhone()
        {
            string result = "";
            for (int i = 0; i < sourceA.Count; i++)
            {
                if (!((string)sourceA[i]).StartsWith("*") && (string)sourceB[i] == "101" && (string)sourceC[i] == "0")
                {
                    result +=   ((string)targetA[i]).PadRight(6, '　') + 
                                ((string)sourceA[i]).PadRight(6, '　') + 
                                ((string)sourceB[i]).PadRight(5, ' ') + 
                                (string)sourceC[i] + " \r\n";
                }

            }
            saving(result);
            System.Diagnostics.Process.Start(SavingFilename);
        }

        public void fine_Have_sA()
        {
            string[] simulationFiles = Directory.GetFiles(Application.StartupPath + "\\模拟测试数据");
            for (int i = 0; i < simulationFiles.Length; i++)
            {
                simulationFiles[i] = Path.GetFileName(simulationFiles[i]);
                if (simulationFiles[i].IndexOf(" ") > 0)
                {
                    simulationFiles[i] = simulationFiles[i].Remove(simulationFiles[i].IndexOf(" "));
                }
                if (simulationFiles[i].IndexOf("(") > 0)
                {
                    simulationFiles[i] = simulationFiles[i].Remove(simulationFiles[i].IndexOf("("));
                }
                if (simulationFiles[i].IndexOf("（") > 0)
                {
                    simulationFiles[i] = simulationFiles[i].Remove(simulationFiles[i].IndexOf("（"));
                }
                if (simulationFiles[i].IndexOf("R") > 0)
                {
                    simulationFiles[i] = simulationFiles[i].Remove(simulationFiles[i].IndexOf("R"));
                }
                if (simulationFiles[i].IndexOf("r") > 0)
                {
                    simulationFiles[i] = simulationFiles[i].Remove(simulationFiles[i].IndexOf("r"));
                }
                simulationFiles[i] = simulationFiles[i].Trim();
            }

            string result = "";
            for (int i = 0; i < sourceA.Count; i++)
            {
                string s = (string)sourceA[i];
                if (!simulationFiles.Contains(s))
                    result += s + "\r\n";
            }
            saving(result);
            System.Diagnostics.Process.Start(SavingFilename);
        }

        public void XLSCreatRegion()
        {
            string result = "";

            string minlonstr = "", maxlonstr = "", minlatstr = "", maxlatstr = "";
            int minlon = 0, minlat = 0, maxlon = 0, maxlat = 0;
            for (int i = 0; i < sourceA.Count; i++)
            {
                int temp = GF.ToDimiDegree(((string)sourceB[i]).Trim());
                if (minlon == 0 || minlon > temp)
                {
                    minlonstr = ((string)sourceA[i]).Trim();
                    minlon = temp;
                }
                if (maxlon == 0 || maxlon < temp)
                {
                    maxlonstr = ((string)sourceA[i]).Trim();
                    maxlon = temp;
                }
                temp = GF.ToDimiDegree(((string)sourceC[i]).Trim());
                if (minlat == 0 || minlat > temp)
                {
                    minlatstr = ((string)sourceA[i]).Trim();
                    minlat = temp;
                }
                if (maxlat == 0 || maxlat < temp)
                {
                    maxlatstr = ((string)sourceA[i]).Trim();
                    maxlat = temp;
                }
            }
            
            int lon = minlon + maxlon;
            lon = lon / 2;
            int lat = minlat + maxlat;
            lat /= 2;
            int lonoffset = maxlon - minlon;
            lonoffset = lonoffset / 2;
            lonoffset = lonoffset * 6 / 10;//unit:0.0001m
            lonoffset = lonoffset / 10;//unit:0.001m

            int latoffset = maxlat - minlat;
            latoffset = latoffset / 2;
            latoffset = latoffset * 6 / 10;//unit:0.0001m
            latoffset = latoffset / 10;//unit:0.001m

            result = "  站名  "+"  经度  "+"  纬度  "+"\r\n";

            result += "   " + maxlatstr + "\r\n"; //name  +minlonstr+maxlonstr+minlatstr+
            result += "\r\n"; //name  +minlonstr+maxlonstr+minlatstr+
            result += minlonstr + "   " + "   " + maxlonstr + "\r\n"; //name  +++minlatstr+
            result += "\r\n"; //name  +minlonstr+maxlonstr+minlatstr+
            result += "   " + minlatstr + "\r\n\r\n";


            result += GF.DimiToSTDDegree(lon) + "  "; //lon
            result += GF.DimiToSTDDegree(lat) + "  "; //lat
            result += lonoffset.ToString() + "  "; //lon width
            result += latoffset.ToString(); //lat width

            saving(result);
            System.Diagnostics.Process.Start(SavingFilename);
        }

        public void XLSdimiToStd()
        {
            MYEXCEL.Excel.Application myexcel = new MYEXCEL.Excel.ApplicationClass();
            MYEXCEL.Excel.Workbook xbook1 = myexcel.Workbooks.Add(true);
            object mo = Missing.Value;
            MYEXCEL.Excel.Worksheet xSheet1 = (MYEXCEL.Excel.Worksheet)xbook1.ActiveSheet;
            xSheet1.Name = "车站";
            MYEXCEL.Excel.Worksheet xSheet2 = (MYEXCEL.Excel.Worksheet)xbook1.Worksheets.Add(mo, xSheet1, 1, mo);
            xSheet2.Name = "线路调度区段1";
            MYEXCEL.Excel.Worksheet xSheet3 = (MYEXCEL.Excel.Worksheet)xbook1.Worksheets.Add(mo, xSheet2, 1, mo);
            xSheet3.Name = "切换点1";

            myexcel.Visible = false;
            string fileDir = Application.StartupPath + "\\RecDataReport\\";
            if (!Directory.Exists(fileDir))
                Directory.CreateDirectory(fileDir);
            fileDir = fileDir + DateTime.Now.ToString("yyyyMMddHHmm") + " xlsChange" + ".xls";
            if (File.Exists(fileDir))
            {
                File.Delete(fileDir);
            }
            xSheet1.Cells[1, 1] = "站名";
            xSheet1.Cells[1, 2] = "经度";
            xSheet1.Cells[1, 3] = "纬度";
            for (int i = 0; i < sourceA.Count; i++)
            {
                xSheet1.Cells[i + 1, 1] = (string)sourceA[i];
                xSheet1.Cells[i + 1, 2] = DimiToStd((string)sourceB[i]);
                xSheet1.Cells[i + 1, 3] = DimiToStd((string)sourceC[i]);
            }
            

            xbook1.SaveAs(fileDir, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                mo, mo, mo, mo, MYEXCEL.Excel.XlSaveAsAccessMode.xlNoChange, mo, mo, mo, mo, mo);
            xbook1 = null;
            xSheet1 = null;
            myexcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject((object)myexcel);
            System.GC.Collect();
            System.Diagnostics.Process.Start(fileDir);
        }

        private string DimiToStd(string dimi)
        {
            dimi += "0";
            
            string d = dimi.Substring(0, dimi.Length - 6);
            string m = dimi.Substring(dimi.Length - 6, 6);

            m = (int.Parse(m) * 6 / 10).ToString("000000");
            m = m.Insert(2, ".");

            return d + m;
        }

        public void saving(string str)
        {
            using (FileStream fs = new FileStream(SavingFilename, FileMode.Create))
            {
                TextWriter wt = new StreamWriter(fs, Encoding.Default);
                fs.Seek(0, SeekOrigin.End);
                wt.WriteLine(str);
                wt.Close();
                fs.Close();
            }
        }

//**************************************************
    }
}
